
clear all
set more off


*  1999 - 2012

forvalues t=1999(1)2012{

use "$data\N_`t'.dta" , clear
duplicates report
duplicates drop 
  
* Check missing on merge variables 
count if w_born_d==. & w_born_m==. & w_born_y==.
count if h_born_d==. & h_born_m==. & h_born_y==. 
count if w_born_d==. & w_born_m==. & w_born_y==. & h_born_d==. & h_born_m==. & h_born_y==. 
drop if w_born_d==. & w_born_m==. & w_born_y==. 
drop if h_born_d==. & h_born_m==. & h_born_y==. 
  
* Generate twin variable
duplicates tag COMUNE PROV mun c_born_d c_born_m c_born_y c_nationality ///
              w_born_d w_born_m w_born_y w_ms w_nationality ///
			  h_born_d h_born_m h_born_y h_ms h_nationality region area , gen (twin)	
tab twin
duplicates drop COMUNE PROV mun c_born_d c_born_m c_born_y c_nationality ///
              w_born_d w_born_m w_born_y w_ms w_nationality ///
			  h_born_d h_born_m h_born_y h_ms h_nationality region area , force	

gen c_birth=string(c_born_d,"%02.0f")+string(c_born_m,"%02.0f")+string(c_born_y,"%04.0f")
global varchange c_birth c_sex c_nationality mun twin
foreach var in $varchange {
rename `var' `var'`t'
}
   
* Drop duplicates on merge variables
duplicates tag  w_born_d w_born_m w_born_y  w_nationality  h_born_d h_born_m h_born_y  h_nationality region , gen(d1)
duplicates tag  w_born_d w_born_m w_born_y  w_nationality  h_born_d h_born_m h_born_y  h_nationality  , gen(d2)
tab d1
tab d2
duplicates drop  w_born_d w_born_m w_born_y  w_nationality h_born_d h_born_m h_born_y  h_nationality region , force
keep w_born_d w_born_m w_born_y  w_nationality  h_born_d h_born_m h_born_y  h_nationality region c_birth`t' c_sex`t' mun`t' c_nationality`t' twin`t' flag`t'

save "$data\N_`t'_v2.dta" , replace
}


* Merge fertility data 1999-2012 : fertility within family

clear all
set more off

use "$data\N_1999_v2.dta" , clear
count
forvalues t=2000/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region  using  "$data\N_`t'_v2.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_1999_final.dta" , replace

use "$data\N_2000_nomerge.dta" , clear
count
forvalues t=2001/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2000_final.dta" , replace

use "$data\N_2001_nomerge.dta" , clear
forvalues t=2002/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2001_final.dta" , replace

use "$data\N_2002_nomerge.dta" , clear
forvalues t=2003/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2002_final.dta" , replace

use "$data\N_2003_nomerge.dta" , clear
forvalues t=2004/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2003_final.dta" , replace

use "$data\N_2004_nomerge.dta" , clear
forvalues t=2005/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2004_final.dta" , replace

use "$data\N_2005_nomerge.dta" , clear
forvalues t=2006/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2005_final.dta" , replace

use "$data\N_2006_nomerge.dta" , clear
forvalues t=2007/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2006_final.dta" , replace

use "$data\N_2007_nomerge.dta" , clear
forvalues t=2008/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2007_final.dta" , replace

use "$data\N_2008_nomerge.dta" , clear
forvalues t=2009/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2008_final.dta" , replace

use "$data\N_2009_nomerge.dta" , clear
forvalues t=2010/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2009_final.dta" , replace

use "$data\N_2010_nomerge.dta" , clear
forvalues t=2011/2012{
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_`t'_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_`t'_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
}
count
save "$data\N_2010_final.dta" , replace

use "$data\N_2011_nomerge.dta" , clear
merge 1:1 w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region using  "$data\N_2012_nomerge.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_2012_nomerge.dta" , replace
restore
drop if _merge==2
drop _merge
count
save "$data\N_2011_final.dta" , replace


* Append fertility data

use "$data\N_1999_final.dta", clear
forval t=2000/2011{
append using "$data\N_`t'_final.dta"
}
append using "$data\N_2012_nomerge.dta"

* Fertility variables by family

global flag flag1999 flag2000 flag2001 flag2002 flag2003 flag2004 flag2005 flag2006 flag2007 flag2008 flag2009 flag2010 flag2011 flag2012
global twin twin1999 twin2000 twin2001 twin2002 twin2003 twin2004 twin2005 twin2006 twin2007 twin2008 twin2009 twin2010 twin2011 twin2012
egen fertility= rowtotal ($flag $twin)
tab fertility
egen twin= rowtotal ( $twin)
tab twin

* First child - birth date
gen str8 c1_birth ="."
replace c1_birth=c_birth1999 if flag1999==1
replace c1_birth=c_birth2000 if flag2000==1 & flag1999==.
replace c1_birth=c_birth2001 if flag2001==1 & flag1999==.  &  flag2000==.
replace c1_birth=c_birth2002 if flag2002==1 & flag1999==.  &  flag2000==. & flag2001==.
replace c1_birth=c_birth2003 if flag2003==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==.
replace c1_birth=c_birth2004 if flag2004==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==.
replace c1_birth=c_birth2005 if flag2005==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==. & flag2004==.
replace c1_birth=c_birth2006 if flag2006==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==. & flag2004==. & flag2005==.
replace c1_birth=c_birth2007 if flag2007==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==. & flag2004==. & flag2005==. & flag2006==.
replace c1_birth=c_birth2008 if flag2008==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==. & flag2004==. & flag2005==. & flag2006==. & flag2007==.
replace c1_birth=c_birth2009 if flag2009==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==. & flag2004==. & flag2005==. & flag2006==. & flag2007==. & flag2008==.
replace c1_birth=c_birth2010 if flag2010==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==. & flag2004==. & flag2005==. & flag2006==. & flag2007==. & flag2008==. & flag2009==.
replace c1_birth=c_birth2011 if flag2011==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==. & flag2004==. & flag2005==. & flag2006==. & flag2007==. & flag2008==. & flag2009==. & flag2010==.
replace c1_birth=c_birth2012 if flag2012==1 & flag1999==.  &  flag2000==. & flag2001==. & flag2002==. & flag2003==. & flag2004==. & flag2005==. & flag2006==. & flag2007==. & flag2008==. & flag2009==. & flag2010==. & flag2011==.

tabstat fertility , stat(mean sd N)

* Keep variables for merge with main MS_final_dataset.dta

keep w_born_d w_born_m w_born_y w_nationality h_born_d h_born_m h_born_y h_nationality region fertility twin c1_birth
save "$data\N_1999-2012.dta" , replace
  
  
  
*  1997 - 1998


forvalues t=1997(1)1998{
	
use "$data\N_`t'.dta" , clear

tab filiazione 
preserve
keep if filiazione>1
save "$data\N_`t'_naturali.dta" , replace
restore
drop if filiazione==2 // ric. solo madre
drop if filiazione==3 // ric. solo padre
drop if filiazione==5 // non riconosciuto

duplicates report
duplicates drop
global vars c_born_mun c_born_prov c_born_m c_born_y m_marriage y_marriage w_born_m w_born_y w_educ w_nationality h_born_m h_born_y h_educ h_nationality region
sum $vars	  

* Check missing on merge variables 
count if  w_born_m==0 & w_born_y==1000 & y_marriage==1000 & m_marriage==0
count if  h_born_m==0 & h_born_y==1000 & y_marriage==1000 & m_marriage==0
drop if  w_born_m==0 & w_born_y==1000 & y_marriage==1000 & m_marriage==0 
drop if  h_born_m==0 & h_born_y==1000 & y_marriage==1000 & m_marriage==0 
drop if  w_born_m==0 & w_born_y==1000 
drop if  h_born_m==0 & h_born_y==1000 
recode w_res_prov (888 = 0)
recode region (88 = 0)
recode w_nationality (988=999)
recode w_nationality (888=999)
recode w_nationality (777=999)
recode h_nationality (988=999)
recode h_nationality (888=999)
recode h_nationality (777=999)

* Generate twin variable
duplicates tag c_born_mun c_born_prov c_born_m c_born_y  ///
              m_marriage y_marriage ///
              w_born_m w_born_y w_educ w_nationality w_res_mun w_res_prov w_prof ///
			  h_born_m h_born_y h_educ h_nationality h_prof region ///
			  gestazione delivery luogo filiazione alive dead, gen (twin)
tab twin	 
duplicates drop c_born_mun c_born_prov c_born_m c_born_y  ///
              m_marriage y_marriage ///
              w_born_m w_born_y w_educ w_nationality w_res_mun w_res_prov w_prof ///
			  h_born_m h_born_y h_educ h_nationality h_prof region ///
			  gestazione delivery luogo filiazione alive dead, force

			  gen c_birth=string(c_born_m,"%02.0f")+string(c_born_y,"%04.0f")
global varchange c_birth c_sex mun twin 
foreach var in $varchange{
rename `var' `var'`t'
}
      
* Drop duplicates on merge variables
duplicates drop m_marriage y_marriage w_born_m w_born_y w_educ w_nationality w_res_prov h_born_m h_born_y h_educ h_nationality region , force
keep m_marriage y_marriage w_born_m w_born_y w_educ w_nationality w_res_prov h_born_m h_born_y h_educ h_nationality region c_birth`t' c_sex`t' mun`t' twin`t' flag`t'
		  
save "$data\N_`t'_v2.dta" , replace
}
 
 
* Merge fertility data 1997-1998 : fertility within family

use "$data\N_1997_v2.dta" , clear
merge 1:1 m_marriage y_marriage w_born_m w_born_y w_educ w_nationality w_res_prov h_born_m h_born_y h_educ h_nationality region using "$data\N_1998_v2.dta"  
tab _merge
preserve 
keep if _merge==2
drop _merge
save "$data\N_1998_final.dta" , replace
restore
drop if _merge==2
drop _merge
count
save "$data\N_1997_final.dta" , replace


* Append fertility data

use "$data\N_1997_final.dta", clear
append using "$data\N_1998_final.dta"
count 

* Fertility variables by family
global flag  flag1997 flag1998 
global twin  twin1997 twin1998
egen fertility= rowtotal ($flag $twin)
tab fertility
egen twin= rowtotal ( $twin)
tab twin

* First child (birth sex residence-mun)
gen str8 c1_birth ="."
replace c1_birth=c_birth1997 if flag1997==1 
replace c1_birth=c_birth1998 if flag1998==1 & flag1997==.
  
tabstat fertility , stat(mean sd N)

* Keep variables for merge with main MS_final_dataset.dta

keep  m_marriage y_marriage w_born_m w_born_y w_educ w_nationality  h_born_m h_born_y h_educ h_nationality region w_res_prov fertility twin c1_birth
save "$data\N_1997-1998.dta" , replace
  
 
  
  
